'for NN and NP, no strike and no put/call option.

'After resolving Trades.TradeTypeId - using clearing_tcs_cpc_xr - then resolve other values


'Gas - Option - Straddle
'Ref#15976, CPC#649530
'Premium = 0.655 / price = .3275
'CPC option get - there is also a put for this same trade. 

'	[RefId] [int] NOT NULL ,
'	[dt_queued] [datetime] NOT NULL ,
'	[clearing_house] [smallint] NULL ,
'	[dt_submitted] [datetime] NULL ,
'	[dt_lastupdate] [datetime] NULL ,
'	[clearing_broker_id] [int] NULL ,
'	[clearing_assigned_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[clearing_status] [int] NULL ,
'	[message_text] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[product_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[trade_price] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[trade_qty] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[first_mmy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[last_mmy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[tm_unit] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[first_cfi] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[last_cfi] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[first_strike] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[last_strike] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[buy_customer_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[buy_clear_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[buy_trader_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[sell_customer_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[sell_clear_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
'	[sell_trader_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

Public Class TcsDataToCPC

	Protected m_refid As Long
	Protected m_clearid As Long

	Protected dbi As dbInterface = Globals.dbi

	Protected m_brokerid As String
	Protected m_prodid As String = ""
	Protected m_tradeprice As String
	Protected m_tradeqty As String
	Protected m_firstmmy As String
	Protected m_lastmmy As String
	Protected m_tmunit As String
	Protected m_firstcfi As String
	Protected m_lastcfi As String
	Protected m_firststrike As String
	Protected m_laststrike As String
	Protected m_buy_custid As String
	Protected m_buy_clearid As String
	Protected m_buy_traderid As String
	Protected m_sell_custid As String
	Protected m_sell_clearid As String
    Protected m_sell_traderid As String
    Protected m_subtype As String ' new for version 1.2
	Protected m_undly_id As String
	Protected m_undly_sectyp As String
	Protected m_undly_mmy As String

	Protected m_cfi2 As String = ""
	Protected m_price As String = ""
	Protected m_clearid2 As Long = 0

    Protected m_execid As String = ""
    Protected m_trade_dt As String = ""
    Protected m_errmsg As String

	Protected m_nymx_cfi As String = ""
	Protected m_trade_datetime As String = ""

	Public QtyType As String = ""

	Public Property ErrMsg() As String
		Get
			Return m_errmsg
		End Get
		Set(ByVal Value As String)
			m_errmsg = Value
		End Set
	End Property

	Public Sub New(ByVal clearid As Long, ByVal refid As Long)
		m_clearid = clearid
		m_refid = refid
	End Sub

	Public Function ConvertData() As Boolean
		' Do the following:
		' - first, get the new trade type
		' - gather existing TCS loan data
		' - convert data over and save to database
		' - determine if there should be more than 1 NYMEX trade
		'   split it out and create a 'related' id on the second one
		' - start verifying data
		'
		pullCPCType()
		If m_prodid = "" Then
			m_errmsg = "No NYMEX Product found for this Trade."
			SetStatus(ClearingStatuses.NoCPCID, m_errmsg)
			Return False
		End If

		Dim sql As String

		sql = "delete from clearing_unresolved where clear_id=" & m_clearid

		dbi.nonquery(sql)

		fillCPCData()

		If m_errmsg <> "" Then
			SetStatus(ClearingStatuses.SubmissionError, m_errmsg)
			Return False
		End If

		moveDataToTable()
		runDataChecks()

		If m_errmsg <> "" Then
			'dont log error message because it is logged in unresolved module
			Return False
		End If

		SubmitForClearing()
		Return True
	End Function

	Public Function CheckData() As Boolean
		loadClearingData()
		SetStatus(ClearingStatuses.GatheringData)
		runDataChecks()

		If m_errmsg <> "" Then
			'dont log error message because it is logged in unresolved module
			Return False
		End If

		SubmitForClearing()
		Return True
	End Function

	Public Function CheckStatus() As Boolean
		loadClearingData()

		If m_errmsg <> "" Then
			'dont log error message because it is logged in unresolved module
			Return False
		End If

		StatusCheck()
		Return True
	End Function

    Public Function VoidRequest() As Boolean
        loadClearingData()

        If m_errmsg <> "" Then
            'dont log error message because it is logged in unresolved module
            Return False
        End If

        Void()
        Return True
    End Function

    Public Sub SubmitForClearing()
        'Dim cpc_logon As LogonMsg = LogonBrokerCPCID()

        'If cpc_logon Is Nothing Then
        '    Exit Sub
        'End If

        loadClearingData()
        SubmitToCPC()

        ' make sure if there is a related clear_id that we also submit it.
        If m_clearid2 <> 0 Then
            m_clearid = m_clearid2
            loadClearingData()
            m_clearid2 = 0
            SubmitToCPC()
        End If

        'cpc_logon.LogonType = UserReqTypes.Logoff
        'cpc_logon.Submit()

    End Sub

    Private Sub SubmitToCPC()

        ' make sure we log the date and time of the original submission

        SetSubmitDate(m_clearid)
        Dim trade As New TradeMsg

        trade.ProductID = Me.m_prodid
        trade.TradePrice = Me.m_tradeprice
        trade.TradeQuantity = Me.m_tradeqty
        trade.FirstMMY = Me.m_firstmmy
        trade.FirstTmUnit = Me.m_tmunit
        trade.FirstCFI = Me.m_firstcfi
		trade.SecTyp = Globals.GetSecTyp(trade.ProductID)

        trade.FirstStrikePrice = Me.m_firststrike
        'trade.SubType = Me.m_subtype
        trade.TradeType = m_subtype ' replacement since we no longer use subtype
		trade.TradeDateTime = m_trade_datetime

        If Me.m_lastmmy <> "" Then
            trade.LastMMY = Me.m_lastmmy
            trade.LastCFI = Me.m_lastcfi
            trade.LastStrikePrice = Me.m_laststrike
        End If

        trade.BuyerData(Me.m_buy_custid, Me.m_buy_clearid, Me.m_buy_traderid)
        trade.SellerData(Me.m_sell_custid, Me.m_sell_clearid, Me.m_sell_traderid)

        trade.PutCall = Globals.TranslatePutCall(trade.FirstCFI)
		If trade.PutCall <> "" Then
			If m_undly_id = "" Then
				Dim und As SpreadReqMsg
				und = UnderlyingID(trade.ProductID, trade.SecTyp)

				trade.UndlyID = und.UndlyID
				trade.UndlySecTyp = und.SecTyp
			Else
				trade.UndlyID = m_undly_id
				trade.UndlySecTyp = m_undly_sectyp
				trade.UndlyMMY = m_undly_mmy
			End If

		End If

		trade.Submit()

		If trade.ErrMsg <> "" Then
			m_errmsg = trade.ErrMsg
			SetStatus(m_clearid, ClearingStatuses.SubmissionError, m_errmsg)
		Else
			UpdateExecID(m_clearid, trade.ExecID, ClearingStatuses.Submitted, trade.TradeDt)
			'If trade.ExecType = "F" Then
			'SetStatus(m_clearid, ClearingStatuses.Cleared)
			'End If
		End If

    End Sub
    Public Sub StatusCheck()
        If m_execid = "" Then
            ' if we do not have an execid, then we never got a response from the CPC system
            ' reset the clearing status so that it resubmits to CPC.
            SetStatus(ClearingStatuses.NewRequest)
            Exit Sub
        End If

        Dim trade As New TradeStatusMsg

        trade.ProductID = Me.m_prodid
        trade.ExecID = Me.m_execid
        trade.TradeDt = Me.m_trade_dt
        trade.Submit()

        If trade.ErrMsg <> "" Then
            m_errmsg = trade.ErrMsg
            SetStatus(ClearingStatuses.SubmissionError, m_errmsg)
		ElseIf trade.ExecType = "0" Then
			SetStatus(ClearingStatuses.Cleared)
        Else
            SetStatus(ClearingStatuses.Submitted)   ' this forces a timestamp update so that we are not stuck in a loop checking the status
        End If
    End Sub

    Public Sub Void()
        Dim trade As New TradeMsg

        trade.ProductID = Me.m_prodid
        trade.ExecID = Me.m_execid
        trade.TradePrice = Me.m_tradeprice
        trade.TradeQuantity = Me.m_tradeqty
        trade.TransType = "1"
		trade.QtyType = QtyType

        trade.Submit()

        If trade.ErrMsg <> "" Then
            m_errmsg = trade.ErrMsg
            SetStatus(ClearingStatuses.SubmissionError, m_errmsg)
        ElseIf trade.TradeAck = "0" Then
            SetStatus(ClearingStatuses.Voided)
        Else
            SetStatus(ClearingStatuses.Submitted)   ' this forces a timestamp update so that we are not stuck in a loop checking the status
        End If
    End Sub

    Private Sub pullCPCType()
        Dim sql As String

        sql = "select * from vwClearingTcsCpcXr where Refid=" & m_refid

        dbi.query(sql)

        If dbi.eof Then
            Exit Sub
        End If

        Me.m_prodid = dbi.getval("cpc_prod_id")
        Me.m_firstcfi = dbi.getval("cfi")

        Me.m_lastcfi = Me.m_firstcfi

        m_cfi2 = dbi.getval("cfi2")
        m_price = dbi.getval("price")
    End Sub

    Private Sub loadClearingData()
        Dim sql As String

        sql = "select * from clearing_trades where clear_id=" & m_clearid

        dbi.query(sql)

        If dbi.eof Then
            m_errmsg = "Error, clearing record not found."
            Exit Sub
        End If
        Me.m_clearid2 = dbi.getval("related_clear_id", 0)
        Me.m_brokerid = dbi.getval("clearing_broker_id")
        Me.m_prodid = dbi.getval("product_id")
        Me.m_tradeprice = dbi.getval("trade_price")
        Me.m_tradeqty = dbi.getval("trade_qty")
        Me.m_tmunit = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(dbi.getval("tm_unit"))
        Me.m_firstcfi = dbi.getval("first_cfi")
        Me.m_firstmmy = dbi.getval("first_mmy")
        Me.m_firststrike = dbi.getval("first_strike")
        Me.m_lastcfi = dbi.getval("last_cfi")
        Me.m_lastmmy = dbi.getval("last_mmy")
        Me.m_laststrike = dbi.getval("last_strike")
        Me.m_buy_custid = dbi.getval("buy_customer_id")
        Me.m_buy_clearid = dbi.getval("buy_clear_id")
        Me.m_buy_traderid = dbi.getval("buy_trader_id")
        Me.m_sell_custid = dbi.getval("sell_customer_id")
        Me.m_sell_clearid = dbi.getval("sell_clear_id")
        Me.m_sell_traderid = dbi.getval("sell_trader_id")
        Me.m_subtype = dbi.getval("subtype")
        Me.m_execid = dbi.getval("clearing_assigned_id")
		m_trade_dt = dbi.getval("trade_dt")
		m_undly_id = dbi.getval("undly_id")
		m_undly_sectyp = dbi.getval("undly_sectyp")
		m_undly_mmy = dbi.getval("undly_mmy")
		m_trade_datetime = dbi.getval("dtm_trade")

        m_subtype = Globals.TranslateSubType(m_subtype)

    End Sub
    'clearing_trades		trades
    'trade_price			TrPricePremium 
    'trade_qty				TrVolume ?
    'first_mmy				TrPStart
    'last_mmy				TrPEnd
    'tm_unit					Based on TVFSeqNo, TradeType.TradeVolFreqId - may need to add TmUnit to TradeVolFreqOpts
    'first_cfi				- based on trade type - added to clearing_tcs_cpc_xr
    'last_cfi				- based on trade type -	added to clearing_tcs_cpc_xr
    'first_strike			TrStrikePrice
    'last_strike			TrStrikePrice
    'buy_customer_id		TrBCompanyId + TrBSeq
    'buy_clear_id			
    'buy_trader_id			TrBTraderId
    'sell_customer_id		TrSCompanyId + TrSSeq
    'sell_clear_id
    'sell_trader_id		TrSTraderId
    Private Sub fillCPCData()

        Dim sql As String
        sql = "select ISNULL(TrVolume * Multiple, TtlAmount) as MonthlyVol, * from vwTradeSummary where RefId=" & m_refid

        dbi.query(sql)

        If dbi.eof Then
            m_errmsg = "Error, Trade Not Found"
            Exit Sub
        End If

        Me.m_tradeprice = dbi.getval("TrPricePremium")
        If Me.m_tradeprice = "0" Then
            Me.m_tradeprice = dbi.getval("TrStrikePrice")
        End If
        'Me.m_tradeqty = dbi.getval("TtlAmount")
		Me.m_tradeqty = dbi.getval("MonthlyVol")
		Me.m_firstmmy = dbi.getval("TrPStart")
		Me.m_lastmmy = dbi.getval("TrPEnd")
		QtyType = "0"
		'If dbi.getval("units").ToString().ToLower() = "contracts" Then
		'QtyType = "1"
		'End If
		Dim fyr As Integer = Year(m_firstmmy)
		Dim fmn As Integer = Month(m_firstmmy)
		Dim lyr As Integer = Year(m_lastmmy)
		Dim lmn As Integer = Month(m_lastmmy)

		' the Monthly volume relfect MMBTU. but if the system is using contracts, then pass in contracts. 
		If (dbi.getval("Units").ToString().ToLower() = "contracts" And (fyr <> lyr Or fmn <> lmn)) Then	' m_firstmmy <> m_lastmmy) Then ' dbi.getval("TradeTypeId").ToString() = "4") Then
			m_tradeqty = dbi.getval("MonthlyVol")
		End If
		Me.m_firststrike = dbi.getval("TrStrikePrice")
		Me.m_buy_custid = dbi.getval("BCoClearId")
		Me.m_buy_traderid = dbi.getval("TrBTrader")
		Me.m_sell_custid = dbi.getval("SCoClearId")
		Me.m_sell_traderid = dbi.getval("TrSTrader")
		Me.m_brokerid = dbi.getval("TrSBrokerId")
		Me.m_laststrike = Me.m_firststrike

		m_trade_datetime = dbi.getval("TrDateTime")
		If m_buy_custid = "" Then
			m_buy_custid = dbi.getval("TrBCoName")
		End If

		If m_sell_custid = "" Then
			m_sell_custid = dbi.getval("TrSCoName")
		End If

		sql = "select TmUnit from TradeVolFreqOpts where TradeVolFreqId=" & dbi.getval("TradeVolFreqId") & " and TVFSeqNo=" & dbi.getval("TVFSeqNo")

		dbi.query(sql)

		If dbi.eof = False Then
			Me.m_tmunit = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(dbi.getval("TmUnit"))
		End If
	End Sub

    Private Function LogonBrokerCPCID() As LogonMsg

        Dim sql As String
        sql = "select nymx_login, nymx_pass from users where UserId=" & m_brokerid
        'sql &= " union select nymx_login, nymx_pass from users where UserId=0"

        dbi.query(sql)

        If dbi.eof Then
            SetStatus(ClearingStatuses.NoCPCID)
            Return Nothing
        End If

        If dbi.getval("nymx_login") = "" Then
            dbi.movenext()
        End If

        Dim logon As New LogonMsg

        'logon.UserName = dbi.getval("nymx_login")
        logon.Password = dbi.getval("nymx_pass")

        logon.LogonType = UserReqTypes.Logon
        logon.Submit()

        If logon.ErrMsg <> "" Then
            SetStatus(ClearingStatuses.LogonFailed)
            m_errmsg = logon.ErrMsg
            Return Nothing
        End If

        Return logon
    End Function

#Region "Data Checks"

    Private Sub runDataChecks()

        ' do the following for data checks:
        ' - validate the buy and sell customers.
        ' - if a customer is valid, validate the trader for that customer.
        ' - validate the year and month for the trade
        ' - validate the strike price
        ' - validate the quantity is a multiple of the quantity block
        ' each item validated will log a row in the [clearing_unresolved] table if it is in error.

        ' need to log in before doing anything else. Get seller broker ID. If no broker ID, log it
        ' for now override and provide test id.
        'Dim cpc_logon As LogonMsg = LogonBrokerCPCID()

        'If cpc_logon Is Nothing Then
        '    Exit Sub
        'End If

        ' 20071009 - remove the pulling of account data on every submission. It is now a manual
        ' task to refresh the account data. It will also likely be added to perform once a day
        ' automatically.
        '		Dim amsg As New AccountsMsg
        Dim atrd As New TradersMsg

        '		amsg.ProductID = Me.m_prodid
        '		amsg.Submit()

        '		If amsg.ErrMsg <> "" Then
        '        m_errmsg = amsg.ErrMsg
        '       Exit Sub
        '		End If

        atrd.ProductID = m_prodid

        'get a list of excluded fields and do not resolve data for them

        Dim ef As New ExcludeFields(m_prodid)

        'if ef.IsExcluded("") = False then
        Dim firmid As String
        firmid = resolveCustomer(Me.m_buy_custid, m_buy_traderid, "buy_customer_id", "buy_clear_id", "buy_trader_id", atrd)
        firmid = resolveCustomer(Me.m_sell_custid, m_sell_traderid, "sell_customer_id", "sell_clear_id", "sell_trader_id", atrd)

        Dim tm As New TermsMsg

        tm.ProductID = m_prodid
		tm.SetReqTyp(Globals.GetSecTyp(m_prodid)) 'Globals.TranslateType(m_firstcfi))
        'tm.ReqTyp = getCfi()
        tm.Submit()

        m_firstmmy = resolveTerm(Me.m_firstmmy, "first_mmy")

        If m_lastmmy <> "" Then
            m_lastmmy = resolveTerm(Me.m_lastmmy, "last_mmy")
        End If

        If ef.IsExcluded("Strk") = False Then
            Dim sp As New StrikesMsg

            sp.ProductID = m_prodid
            sp.StartTerm = m_firstmmy
            sp.EndTerm = m_lastmmy

            '            sp.Submit()

            If sp.ValidateStrike(m_firststrike) = False Then
                Dim urd As New UnresolvedData(m_clearid, m_clearid2)
                urd.ColumnName = "first_strike"
                urd.DataTable = "nymx_prod_strikes"
                urd.DataIdField = "strike_price"
                urd.DataDescField = "strike_price"
                urd.ColumnName2 = "last_strike"
                urd.DataIdField2 = "strike_price"
                If m_lastmmy = "" Then
                    urd.Where = "prod_id=" & dbi.quote(m_prodid) & " and first_mmy=" & dbi.quote(m_firstmmy) & " and last_mmy=" & dbi.quote(m_firstmmy)
                Else
                    urd.Where = "prod_id=" & dbi.quote(m_prodid) & " and first_mmy=" & dbi.quote(m_firstmmy) & " and last_mmy=" & dbi.quote(m_lastmmy)
                End If
                urd.Insert()

                Me.m_errmsg = "Unresolved Data"
            End If
        End If

        ' do not need to validate last strike - it should always be set to the first strike price.

        If resolveQuantity() = False Then
            m_errmsg = "Unresolved Data"
        End If

        'cpc_logon.LogonType = UserReqTypes.Logoff
        'cpc_logon.Submit()

    End Sub

    Private Function resolveCustomer(ByVal customer As String, ByVal trader As String, ByVal dbcol As String, ByVal clearcol As String, ByVal tradercol As String, ByRef atrd As TradersMsg) As String
        Dim sql As String

        sql = "select * from nymx_prod_accounts where prod_id=" & dbi.quote(m_prodid) & " and (exec_firm_name = " & dbi.quote(customer) & " or acct_id=" & dbi.quote(customer) & ")"

        dbi.query(sql)

        If dbi.eof Then
            sql = "select * from nymx_prod_accounts where prod_id=" & dbi.quote(m_prodid) & " and exec_firm_name like " & dbi.quote(customer & "%")

            dbi.query(sql)

            If dbi.eof Then
                ' check to see if a default value for this specific customer is defined. 
                sql = "select cpc_data from clearing_data_xr where prod_id=" & dbi.quote(m_prodid) & " and data_type=" & dbi.quote(dbcol) & " and tcs_data=" & dbi.quote(customer)

                dbi.query(sql)

                If dbi.eof Then
                    ' if customerid not found, then log it to error table for manual resolution
                    Dim where As String = "prod_id=" & dbi.quote(m_prodid)
                    Dim urd As New UnresolvedData(m_clearid, m_clearid2, dbcol, "nymx_prod_accounts", "acct_id", "exec_firm_name", where, clearcol, "clear_firm_id")
                    urd.Insert()

                    Me.m_errmsg = "Unresolved Data"
                    Return ""
                End If

                Dim cpcdata As String = dbi.getval("cpc_data")

                sql = "select * from  nymx_prod_accounts where prod_id=" & dbi.quote(m_prodid) & " and acct_id=" & dbi.quote(cpcdata)

                dbi.query(sql)
                If dbi.eof Then
                    ' if the default data isnt found, then we need to delete the default data row and log the unresolved data
                    sql = "delete from  clearing_data_xr where prod_id=" & dbi.quote(m_prodid) & " and data_type=" & dbi.quote(dbcol) & " and tcs_data=" & dbi.quote(customer)

                    dbi.nonquery(sql)

                    Dim where As String = "prod_id=" & dbi.quote(m_prodid)
                    Dim urd As New UnresolvedData(m_clearid, m_clearid2, dbcol, "nymx_prod_accounts", "acct_id", "exec_firm_name", where, clearcol, "clear_firm_id")
                    urd.Insert()

                    Me.m_errmsg = "Unresolved Data"
                    Return ""
                End If
            End If
        End If

        Dim custid As String
        Dim clearfid As String
        Dim execfirm As String

        custid = dbi.getval("acct_id")
        clearfid = dbi.getval("clear_firm_id")
        execfirm = dbi.getval("exec_firm_id")

        sql = "update clearing_trades set " & dbcol & "=" & dbi.quote(custid)
        sql &= ", " & clearcol & "=" & dbi.quote(clearfid)
        sql &= " where clear_id=" & Me.m_clearid

        If m_clearid2 <> 0 Then
            sql &= " or clear_id=" & Me.m_clearid2
        End If

        dbi.nonquery(sql)

        atrd.FirmID = execfirm
        atrd.Submit()

        resolveTrader(execfirm, trader, tradercol)

        Return custid
    End Function

    Private Function resolveTrader(ByVal firmid As String, ByVal traderid As String, ByVal dbcol As String) As Boolean
        Dim sql As String

        sql = "select * from nymx_firm_traders where firm_id = " & dbi.quote(firmid) & " and (trader_nm = " & dbi.quote(traderid) & " or trader_id=" & dbi.quote(traderid) & ")"

        dbi.query(sql)

        If dbi.eof Then
            sql = "select * from nymx_firm_traders where firm_id  = " & dbi.quote(firmid) & " and trader_nm like " & dbi.quote(traderid & "%")

            dbi.query(sql)

            If dbi.eof Then
                ' check to see if a default value for this specific customer is defined. 
                sql = "select cpc_data from clearing_data_xr where prod_id=" & dbi.quote(m_prodid) & " and data_type=" & dbi.quote(dbcol) & " and tcs_data=" & dbi.quote(traderid)

                dbi.query(sql)

                If dbi.eof Then
                    ' if customerid not found, then log it to error table for manual resolution
                    Dim where As String = "firm_id=" & dbi.quote(firmid)
                    Dim urd As New UnresolvedData(m_clearid, m_clearid2, dbcol, "nymx_firm_traders", "trader_id", "trader_nm", where, "", "")
                    urd.Insert()

                    Me.m_errmsg = "Unresolved Data"
                    Return False
                End If

                Dim cpcdata As String = dbi.getval("cpc_data")

                sql = "select * from nymx_firm_traders where firm_id  = " & dbi.quote(firmid) & " and trader_id=" & dbi.quote(cpcdata)

                dbi.query(sql)

                If dbi.eof Then
                    ' if the default data isnt found, then we need to delete the default data row and log the unresolved data
                    sql = "delete from  clearing_data_xr where prod_id=" & dbi.quote(m_prodid) & " and data_type=" & dbi.quote(dbcol) & " and tcs_data=" & dbi.quote(traderid)

                    dbi.nonquery(sql)

                    Dim where As String = "firm_id=" & dbi.quote(firmid)
                    Dim urd As New UnresolvedData(m_clearid, m_clearid2, dbcol, "nymx_firm_traders", "trader_id", "trader_nm", where, "", "")
                    urd.Insert()

                    Me.m_errmsg = "Unresolved Data"
                    Return ""
                End If
            End If
        End If

        Dim trader As String

        trader = dbi.getval("trader_id")

        sql = "update clearing_trades set " & dbcol & "=" & dbi.quote(trader)
        sql &= " where clear_id=" & Me.m_clearid

        If m_clearid2 <> 0 Then
            sql &= " or clear_id=" & Me.m_clearid2
        End If

        dbi.nonquery(sql)

        Return True
    End Function
    'PLA20080402 - change the list of products that need a month, day and year to resolve from the database.
    ' build a list of products from the database and check to see if the current prod id qualifies.
    Private Function isLongDate() As Boolean
        Dim sql As String = "select count(*) as rowsc from nymx_options where type_id='longdate' and prod_id = " & dbi.quote(m_prodid)

        dbi.query(sql)

        Dim rc As Long = dbi.getval("rowsc")

        If rc > 0 Then
            Return True
        End If

        Return False
    End Function

    Private Function resolveTerm(ByVal term As String, ByVal dbcol As String) As String
        Dim sql As String
        Dim mmy As String
        Dim tterm As Date

        '        If m_prodid = "KD" Or m_prodid = "CD" Then ' we need the day as well as month and year
        If isLongDate() Then
            If IsNumeric(term) And term.Length = 8 Then
                mmy = term
            Else
                If term = "" Then
                    mmy = ""
                Else
                    tterm = Date.Parse(term)
                    mmy = tterm.Year().ToString() & tterm.Month.ToString.PadLeft(2, "0") & tterm.Day.ToString.PadLeft(2, "0")
                End If
            End If
        Else
            If IsNumeric(term) And term.Length = 6 Then
                mmy = term
            Else
                If term = "" Then
                    mmy = ""
                Else
                    tterm = Date.Parse(term)
                    mmy = tterm.Year().ToString() & tterm.Month.ToString.PadLeft(2, "0")
                End If
            End If
        End If

		sql = "select * from nymx_prod_terms where prod_id = " & dbi.quote(m_prodid) & " and term = " & dbi.quote(mmy)

        dbi.query(sql)

		'Dim rc As Long = dbi.getval("rowsc")
		Dim undly_id As String = ""
		Dim undly_sectyp As String = ""
		Dim undly_mmy As String = ""

		If dbi.eof() Then
			' if customerid not found, then log it to error table for manual resolution
			Dim where As String = "prod_id=" & dbi.quote(m_prodid)
			Dim urd As New UnresolvedData(m_clearid, m_clearid2, dbcol, "nymx_prod_terms", "term", "term", where, "", "")
			urd.Insert()

			Me.m_errmsg = "Unresolved Data"
			Return ""
		Else
			undly_id = dbi.getval("undly_id")
			undly_sectyp = dbi.getval("undly_sectyp")
			undly_mmy = dbi.getval("undly_mmy")
		End If

		sql = "update clearing_trades set " & dbcol & "=" & dbi.quote(mmy)
		sql &= ", undly_id=" & dbi.quote(undly_id)
		sql &= ", undly_sectyp=" & dbi.quote(undly_sectyp)
		sql &= ", undly_mmy=" & dbi.quote(undly_mmy)
		sql &= " where clear_id=" & Me.m_clearid

		If m_clearid2 <> 0 Then
			sql &= " or clear_id=" & Me.m_clearid2
		End If

		dbi.nonquery(sql)

		Return mmy
	End Function

    Private Function resolveQuantity() As Boolean
		' going to try and see if data can get resolved properly
		Return True

		' unused for now.
		' 
		Dim sql As String

        sql = "select quantity_block, cfi from nymx_products where prod_id=" & dbi.quote(m_prodid)

        dbi.query(sql)

        If dbi.eof Then
            Exit Function
        End If

        Dim qty As Single = Convert.ToSingle(dbi.getval("quantity_block"))
        m_nymx_cfi = dbi.getval("cfi")

        If m_tradeqty = "" Then
            Dim urd As New UnresolvedData(m_clearid, m_clearid2)
            urd.ColumnName = "trade_qty"
            urd.Where = "Must be a multiple of " & qty
            urd.Insert()

            Me.m_errmsg = "Unresolved Data"
            Return False

        End If

        Dim mqty As Single = Convert.ToSingle(Me.m_tradeqty)

        If mqty Mod qty <> 0 Then
            Dim urd As New UnresolvedData(m_clearid, m_clearid2)
            urd.ColumnName = "trade_qty"
            urd.Where = "Must be a multiple of " & qty
            urd.Insert()

            Me.m_errmsg = "Unresolved Data"
            Return False
        End If

        Return True
    End Function

    Private Function getCfi() As String
        Dim sql As String

        sql = "select cfi from nymx_products where prod_id=" & dbi.quote(m_prodid)

        dbi.query(sql)

        If dbi.eof Then
            Return ""
            Exit Function
        End If

        Return dbi.getval("cfi")
    End Function

	Private Function UnderlyingID(ByVal id As String, ByVal sect As String) As SpreadReqMsg
		Dim sr As New SpreadReqMsg

		sr.ProductID = id
		sr.SecTyp = sect
		sr.Submit()

		Return sr
	End Function

#End Region

    Private Sub moveDataToTable()
        Dim sql As String
        Dim andsql As String = ""

        ' before sending data to table, determine if it is a 1 month or multi-month trade

        Dim fmmy As Date = Date.Parse(m_firstmmy)
        Dim lmmy As Date = Date.Parse(m_lastmmy)

        If fmmy.Month = lmmy.Month And fmmy.Year = lmmy.Year Then
            m_lastmmy = ""
        End If

        'if this trade should be split into two nymex trades, then do that here
        If m_cfi2 <> "" Then
            If m_price <> "" Then
                If m_price = "TrPricePremium/2" Then
                    m_tradeprice = (Convert.ToDouble(m_tradeprice) / 2).ToString()
                End If
            End If

            sql = "insert into clearing_trades (related_clear_id, RefId, dt_queued,clearing_house, broker_display) values ("
            sql &= m_clearid & ", " & m_refid & ", getdate(), 43,1)"

            dbi.nonquery(sql)

            sql = "select @@IDENTITY as clearid"

            dbi.query(sql)

            m_clearid2 = dbi.getval("clearid")

            andsql = " or clear_id=" & m_clearid2
        End If

        sql = "update clearing_trades set "
		sql &= "clearing_status=" & ClearingStatuses.GatheringData.GetHashCode & ",message_text='',"
        sql &= "clearing_broker_id=" & m_brokerid & ","
        sql &= "product_id=" & dbi.quote(m_prodid) & ","
        sql &= "trade_price=" & dbi.quote(m_tradeprice) & ","
        sql &= "trade_qty=" & dbi.quote(m_tradeqty) & ","
        sql &= "first_mmy=" & dbi.quote(m_firstmmy) & ","
        sql &= "tm_unit=" & dbi.quote(m_tmunit) & ","
        sql &= "first_cfi=" & dbi.quote(m_firstcfi) & ","
        sql &= "first_strike=" & dbi.quote(m_firststrike) & ","

        If m_lastmmy <> "" Then
            sql &= "last_mmy=" & dbi.quote(m_lastmmy) & ","
            sql &= "last_cfi=" & dbi.quote(m_lastcfi) & ","
            sql &= "last_strike=" & dbi.quote(m_laststrike) & ","
        End If

		sql &= "dtm_trade=" & dbi.quote(m_trade_datetime) & ","
        sql &= "buy_customer_id=" & dbi.quote(m_buy_custid) & ","
        sql &= "buy_trader_id=" & dbi.quote(m_buy_traderid) & ","
        sql &= "sell_customer_id=" & dbi.quote(m_sell_custid) & ","
        sql &= "sell_trader_id=" & dbi.quote(m_sell_traderid)

        sql &= " where clear_id=" & m_clearid & andsql

        dbi.nonquery(sql)

        If m_clearid2 <> 0 Then
            sql = "update clearing_trades set related_clear_id=" & m_clearid2
            sql &= " where clear_id=" & m_clearid

            dbi.nonquery(sql)

            sql = "update clearing_trades set first_cfi=" & dbi.quote(m_cfi2)
            If m_lastmmy <> "" Then
                sql &= ", last_cfi=" & dbi.quote(m_cfi2)
            End If
            sql &= " where clear_id=" & m_clearid2

            dbi.nonquery(sql)
		End If

		dbi.SetupStoredProc("sp_clearing_cleanup")
		dbi.RunStoredProc()

    End Sub

    Private Sub SetStatus(ByVal newstat As ClearingStatuses)
        SetStatus(newstat, "")
    End Sub

    Private Sub SetStatus(ByVal newstat As ClearingStatuses, ByVal msgtxt As String)
        Dim sql As String

        sql = "update clearing_trades set message_text=" & dbi.quote(msgtxt) & ", clearing_status=" & newstat.GetHashCode
        sql &= ", dt_lastupdate=getdate()"
        sql &= " where clear_id in (" & m_clearid & "," & m_clearid2 & ")"

        dbi.nonquery(sql)
    End Sub

    Private Sub UpdateExecID(ByVal execid As String, ByVal newstat As ClearingStatuses)
        Dim sql As String

        sql = "update clearing_trades set clearing_assigned_id=" & dbi.quote(execid) & ", clearing_status=" & newstat.GetHashCode
        sql &= ", dt_lastupdate=getdate()"
        sql &= " where clear_id in (" & m_clearid & "," & m_clearid2 & ")"

        dbi.nonquery(sql)
    End Sub

    Private Sub SetSubmitDate()
        Dim sql As String

        sql = "update clearing_trades set dt_submitted=getdate() where clear_id in (" & m_clearid & "," & m_clearid2 & ")"

        dbi.nonquery(sql)
    End Sub

    Private Sub SetStatus(ByVal clearid As Long, ByVal newstat As ClearingStatuses)
        SetStatus(clearid, newstat, "")
    End Sub

    Private Sub SetStatus(ByVal clearid As Long, ByVal newstat As ClearingStatuses, ByVal msgtxt As String)
        Dim sql As String

        sql = "update clearing_trades set message_text=" & dbi.quote(msgtxt) & ", clearing_status=" & newstat.GetHashCode
        sql &= ", dt_lastupdate=getdate()"
        sql &= " where clear_id in (" & clearid & ")"

        dbi.nonquery(sql)
    End Sub

	Private Sub UpdateExecID(ByVal clearid As Long, ByVal execid As String, ByVal newstat As ClearingStatuses, ByVal tradeDt As String)
		Dim sql As String

		sql = "update clearing_trades set clearing_assigned_id=" & dbi.quote(execid) & ", clearing_status=" & newstat.GetHashCode
		sql &= ", trade_dt='" & tradeDt & "'"
		sql &= ", dt_lastupdate=getdate()"
		sql &= " where clear_id in (" & clearid & ")"
		'		sql &= " where clear_id in (" & m_clearid & "," & m_clearid2 & ")"

		dbi.nonquery(sql)
	End Sub

    Private Sub SetSubmitDate(ByVal clearid As Long)
        Dim sql As String

        sql = "update clearing_trades set dt_submitted=getdate() where clear_id in (" & clearid & ")"

        dbi.nonquery(sql)
    End Sub
End Class

